Preliminaries

There are a number of reasons you might want to backup and restore data to/from a database server. Perhaps you’ve been working on an application that connects to a local database server and are ready to deploy it to production. It would certainly be a pain to have to re-enter all the information you’ve already inserted into the database. As another example, you certainly want to backup your database server if you’re running a production application providing critical services or containing sensitive user data. In either case, MariaDB comes with a tool called mysqldump that allows you to backup your database server. We’ll show how to backup a single database, multiple databases, and the entire database server.

Prerequisites

We’ll assume that you have already installed MariaDB on your server and that you have configured a non-root user with sudo privileges. In addition, you should have at least one If you haven’t done so yet, check out our detailed “Getting Started With Your Stack” tutorial to see how to create a non-root user and connect to your Stack using SSH. If you haven’t installed MariaDB yet, we’ve written a tutorial on how to get started. Finally, check out our “Writing Basic SQL Queries With MariaDB” tutorial for information on how to insert some records into your database.

Performing Some Dumps

In order to perform an SQL dump on a database, you will need to have a user that has access privileges to that database. That also means that in order to perform an SQL dump on the entire database server, you will either need to have a database user that has access to all databases or you will need to perform the SQL dump as the root database user. For this tutorial, we’ll be using the admin database user that has access privileges to every single database on our MariaDB server.

Let’s suppose that I have three databases on my remote server named source, sink, and test. The source database contains some arbitrary number of tables with many records that I wish to backup. The sink database contains no tables, and I wish to move all the tables from source into this database. Finally, test also contains a number of tables and records. Firstly, to perform an SQL dump on the source database, run the following command:

mysqldump -u admin --password=$ADMIN_PASSWORD source > source.sql

We’ve stored the password to the admin database user in an environment variable called ADMIN_PASSWORD. To dissect the command above, we’ve passed in our MariaDB credentials to the mysqldump command and specified the source database to be dumped. The > operator outputs the contents of the mysqldump command into a file called source.sql. Using this example, the command to perform an SQL dump on a number of databases on your database server is not much more complicated. Suppose you had databases named database1, database2, … databaseN, you could back up any number of them by passing the database name as a parameter to the mysqldump command.

mysqldump -u admin --password=$ADMIN_PASSWORD database1 database2 database3 database23 > lots_of_dbs.sql

Performing an SQL Dump on the Database Server

Next, we’ll perform an SQL dump on the entire database server. To do so, run the following command:

mysqldump -u admin --password=$ADMIN_PASSWORD --all-databases > all_databases.sql

The syntax of this command is very similar to the previous command. We’ve specified our MariaDB credentials to the mysqldump command once again. However, instead of passing a database server to perform an SQL dump on, we’ve passed in the --all-databases flag, whose function is quite clear. We’ve redirected the output of this command into a file appropriately named all_databases.sql. Now we’re ready to load data back into our MariaDB server.

Restoring Data

We’ve generated two files: source.sql and all_databases.sql. As previously mentioned, we want to import the data from the source.sql file into the sink database. The syntax to do this is actually very simple. Run the following command:

mysql -u admin --password=$ADMIN_PASSWORD sink < source.sql

If you aren’t shown any error messages, that usually meant the process went well! Try and authenticate with the MariaDB client and see if the data was loaded into the sink database by running the following SQL commands.

USE sink;
SHOW tables;

You should be shown all the databases that were in the source database. To see if the records were actually backed up, run a SELECT * from sink.TABLE_NAME; command, replacing TABLE_NAME with the name of a table in your personal sink database.

Now that we know how to load data into a single database, we can try dropping some databases and restoring the SQL dump of the entire database server. Run the following commands to delete the three databases that exist in our database (be sure that these databases do not have any important data, as we cannot guarantee the restore will proceed successfully). Authenticate with the MariaDB client and run the following SQL commands:

DROP DATABASE source;
DROP DATABASE sink;
DROP DATABASE test;

Now if you run SHOW DATABASES;, you should not see those three databases anymore. We’re now going to try and reload all of the databases using the all_databases.sql file. Run the following command on your remote machine:

mysql -u admin --password=$ADMIN_PASSWORD < all_databases.sql

To verify that everything worked correctly, authenticate with the MariaDB client again and run the SHOW DATABASES; command. Hopefully, all your databases that you dropped earlier were restored. For future reference, dropped databases isn’t the only data you can restore. In the all_databases.sql file, there are SQL commands to drop the database that is being restored if it exists, recreate the database, and enter all the information back into the database. So if you have made a number of changes but you wish to revert to a previous SQL dump, you can use the same command.

Final Words

You’ve learned another vital task in database management. Keeping periodic backups is essential to making sure that you are not completely devastated by catastrophic changes to your database server. The .sql files that are created by the mysqldump tool can be backed up and moved around between your machine just like you would any other text file. For more information pertaining to database and server administration, check out our Community Section for more tutorials. From all of us at Stack Harbor, ahoy!